import { executeSql } from "../util/db.js";



export const selectArticlesCount = function () {
    let sql = "select count(id) count from articles";
    return executeSql(sql);
}
export const selectArticlesRandom = function (start = 0) {
    let sql = "select id,cover,title from articles limit ?,5";
    return executeSql(sql, [start]);
}

export const selectArticlesCatrgory = function () {
    let sql = "select id,name from categories";
    return executeSql(sql);
}

export const selectArticlesRank = function () {
    let sql = "select id,title from articles order by `read` desc limit 0,7";
    return executeSql(sql);
}
export const selectArticlesLatest = function () {
    let sql = "select a.id,a.title,a.content,a.cover,c.`name`,a.read,count(m.id) comments,a.date from articles a left join categories c on a.categoryId=c.id left join comments m on a.id=m.articleId group by a.id order by a.date desc limit 0,5;";
    return executeSql(sql);
}

export const selectArticlesComment = function () {
    let sql = "select author,date,content intro from comments order by `date` desc,`time` desc limit 0,6;";
    return executeSql(sql);
}

export const selectArticlesAttention = function (start = 0) {
    let sql = "select content intro from articles limit ?,7";
    return executeSql(sql, [start]);
}

//文章详细内容
export const selectArticleDetail = function (id) {
    let sql = `select a.title,a.author,t.name as type,a.date,a.read,count(c.id) as comments,a.content from 
                    articles a left join categories t on a.categoryId=t.id
                               left join comments c on a.id=c.articleId
                where isDelete=0 and a.id=?`;
    return executeSql(sql, [id]);
}

//上一篇文章
export const selectPreArticle = function (cid) {
    let sql = "select id,title from articles where isDelete=0 and id<? ORDER BY id DESC LIMIT 1";
    return executeSql(sql, [cid]);
}

//下一篇
export const selectNextArticle = function (cid) {
    let sql = "select id,title from articles where isDelete=0 and id>? ORDER BY id asc LIMIT 1";
    return executeSql(sql, [cid]);
}


//文章条件搜索
export const selectConditionArticle = function (startIndex, pageSize, type, key) {
    let sql = 'select * from articles where isDelete=0';
    let params = [];

    //方法1 动态参数
    if (type != null || type != "") {
        sql += " and categoryId = ?";
        params.push(type);
    }

    if (key != null && key != "") {
        sql += " and title like ?";
        params.push('%' + key + '%');
    }

    sql += ' limit ?,?';
    console.log(sql);

    params.push(startIndex);
    params.push(pageSize);
    return executeSql(sql, params);
};


/*
    condition={
        equies:{
            id:1,
            date:"asdasd"
        },
        like:{
            name:"%游戏%"
        },
        ……
    }
    条件关键
    equies - 等于查询（=）
    like - 模糊查询（like）
    gt - 大于查询（>）
    lt - 小于查询（<）
    ge - 大于等于查询（>=）
    le - 小于等于查询（<=）
    neq - 不等于查询（!=）
    in - IN 查询（in (...)）
    notIn - NOT IN 查询（not in (...)）
    between - BETWEEN 查询（between ... and ...）
*/
export const selectConditionArticle2 = function (startIndex, pageSize, condition = {}) {
    let sql = 'select * from articles where isDelete=0 ';
    let params = [];

    // 方式2：动态sql
    for (let key in condition) {
        const conditionGroup = condition[key];

        // 等于条件
        if (key === 'equies') {
            for (let field in conditionGroup) {
                sql += ` and ${field} = ? `;
                params.push(conditionGroup[field]);
            }
        }
        // 模糊查询条件
        else if (key === 'like') {
            for (let field in conditionGroup) {
                sql += ` and ${field} like ? `;
                params.push(`%${conditionGroup[field]}%`);
            }
        }
        // 大于条件
        else if (key === 'gt') {
            for (let field in conditionGroup) {
                sql += ` and ${field} > ? `;
                params.push(conditionGroup[field]);
            }
        }
        // 小于条件
        else if (key === 'lt') {
            for (let field in conditionGroup) {
                sql += ` and ${field} < ? `;
                params.push(conditionGroup[field]);
            }
        }
        // 大于等于条件
        else if (key === 'ge') {
            for (let field in conditionGroup) {
                sql += ` and ${field} >= ? `;
                params.push(conditionGroup[field]);
            }
        }
        // 小于等于条件
        else if (key === 'le') {
            for (let field in conditionGroup) {
                sql += ` and ${field} <= ? `;
                params.push(conditionGroup[field]);
            }
        }
        // 不等于条件
        else if (key === 'neq') {
            for (let field in conditionGroup) {
                sql += ` and ${field} != ? `;
                params.push(conditionGroup[field]);
            }
        }
        // IN查询条件
        else if (key === 'in') {
            for (let field in conditionGroup) {
                // 确保值是数组
                if (Array.isArray(conditionGroup[field])) {
                    const placeholders = conditionGroup[field].map(() => '?').join(',');
                    sql += ` and ${field} in (${placeholders}) `;
                    params.push(...conditionGroup[field]);
                }
            }
        }
        // NOT IN查询条件
        else if (key === 'notIn') {
            for (let field in conditionGroup) {
                // 确保值是数组
                if (Array.isArray(conditionGroup[field])) {
                    const placeholders = conditionGroup[field].map(() => '?').join(',');
                    sql += ` and ${field} not in (${placeholders}) `;
                    params.push(...conditionGroup[field]);
                }
            }
        }
        // BETWEEN条件
        else if (key === 'between') {
            for (let field in conditionGroup) {
                // 确保值是包含两个元素的数组
                if (Array.isArray(conditionGroup[field]) && conditionGroup[field].length === 2) {
                    sql += ` and ${field} between ? and ? `;
                    params.push(conditionGroup[field][0], conditionGroup[field][1]);
                }
            }
        }
    }

    sql += ' limit ?,? ';
    console.log(sql);

    params.push(startIndex);
    params.push(pageSize);
    return executeSql(sql, params);
};

//根据id获取文章信息
export const selectArticleById = function (id) {
    let sql = "select id,title,cover,categoryId,date,content from articles where id=?";
    return executeSql(sql, [id]);
}

//删除文章
export const deleteArticleById = function (id) {
    let sql = "delete from articles where id=? and isDelete=0";
    return executeSql(sql, [id]);
}

//文章编辑
export const updateArticleById = function (id, title, cover, categoryId, date, content, state) {
    let sql = "update articles set title=?,cover=?,categoryId=?,date=?,content=?,state=? where id=? and isDelete=0";
    return executeSql(sql, [title, cover, categoryId, date, content, state, id]);
}



